﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;
using System.Data;

namespace Framework.Db
{
    /// <summary>
    /// Oracle数据端
    /// </summary>
    public class OracleHelper : IDataProvider
    {
        string connStr = null;

        OracleConnection mycn = new OracleConnection();
        OracleCommand mycm = new OracleCommand();

        /// <summary>
        /// Orcale数据库连接
        /// </summary>
        public OracleHelper()
        {
            if (mycn.State == ConnectionState.Closed)
            {
                initConnStr();
                try
                {
                    mycn.Open();
                }
                catch (OracleException ex)
                {
                    Kit.other.WriteErrLog("Oracle连接数据库",ex);
                }
            }
        }

        /// <summary>
        /// 获取数据库连接参数信息
        /// </summary>
        private void initConnStr()
        {
            connStr = string.Format("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SID={2})));User Id={3};Password={4};", Kit.AppOpr.GetConfigStrings("db_host"), Kit.AppOpr.GetConfigStrings("db_port"), Kit.AppOpr.GetConfigStrings("db_name"), Kit.AppOpr.GetConfigStrings("db_user"), Kit.AppOpr.GetConfigStrings("db_pwd"));
            mycn.ConnectionString = connStr;
        }

        /// <summary>
        /// 对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于其他所有类型的语句，返回值为 -1   
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>影响行数,否则-1</returns>
        public int ExecuteNonQuery(string sql)
        {
            int i = -1;
            try
            {
                if (sql == null || sql == string.Empty)
                {
                    return i;
                }
                else
                {
                    mycm = new OracleCommand(sql, mycn);
                    i = mycm.ExecuteNonQuery();
                }

            }
            catch (OracleException ex)
            {
                Kit.other.WriteErrLog("Oracle执行SQL,返回影响行数", ex);
            }
            return i;
        }

        /// <summary>
        /// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
        /// </summary>
        /// <param name="sql">SELECT 语句</param>
        /// <returns>.NET Framework 数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用</returns>
        public object ExecuteScalar(string sql)
        {
            object i = null;
            try
            {
                if (sql == null || sql == string.Empty)
                {
                    return i;
                }
                else
                {
                    mycm = new OracleCommand(sql, mycn);
                    i = mycm.ExecuteScalar();
                }
            }
            catch (OracleException ex)
            {
                Kit.other.WriteErrLog("执行单条SQL,返回第一行的第一列", ex);
            }
            return i;
        }

        /// <summary>
        /// 执行单Sql语句查询，并将查询返回的结果作为一个数据集返回 
        /// </summary>
        /// <param name="sql">SELECT 语句</param>
        /// <returns>数据集 DataSet</returns>
        public System.Data.DataSet RetriveDataSet(string sql)
        {
            if (sql == null || sql == string.Empty)
            {
                return null;
            }
            using (OracleDataAdapter da = new OracleDataAdapter(sql, mycn))
            {
                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds);
                }
                catch (Exception ex)
                {
                    Kit.other.WriteErrLog("Oracle执行SQL,返回DataSet",ex);
                }
                return ds;
            }
        }


        public System.Data.DataSet RetriveDataSet(string[] sql, params string[] tableName)
        {
            System.Data.DataSet ds = null;
            return ds;
        }

        public System.Data.DataSet UpdateDataSet(string sql, System.Data.DataSet hasChangesDataSet)
        {
            System.Data.DataSet ds = null;
            return ds;
        }

        public void Dispose()
        {
            mycn.Close();
        }
    }
}
